import json

import xlwt

from quote_folder.Db_connect import DB_connect

develop_con = DB_connect().online_db()  # 开发数据库连接
bigdata_con = DB_connect().online_big_data()
cur1 = develop_con.cursor()
cur2 = bigdata_con.cursor()

info_list = []


def get_is_stop(sqh, item):
    sql = f"SELECT name, is_stop, unique_number FROM `mykhadmin`.`finals` WHERE `application_number` = 'ZL{sqh}'"
    cur1.execute(sql)
    data = cur1.fetchall()
    try:
        if data[0][1] == 0:
            item['name'] = data[0][0]
            item['is_stop'] = "监管"
            item['unique_number'] = data[0][2]
        else:
            item['name'] = data[0][0]
            item['is_stop'] = "不监管"
            item['unique_number'] = data[0][2]
    except:
        item['name'] = "不在系统监管专利内"
    return item


def getznj():
    sql = "SELECT DISTINCT application_number FROM overdue_fine_0321"
    cur2.execute(sql)
    data = cur2.fetchall()
    znj_li = [i[0] for i in data]
    return znj_li


def deal_znj():
    znj_li = getznj()
    for sqh in znj_li:
        item = {}
        sql1 = f"SELECT * FROM `big_data`.`overdue_fine_0321` WHERE `application_number` = '{sqh}' and payment_end_time>NOW() limit 1"
        cur2.execute(sql1)
        data = cur2.fetchall()
        if data:
            print(sqh)
            item['sqh'] = sqh
            item['jzr'] = data[0][3]
            money1 = data[0][5]
            sql = f"SELECT * FROM `big_data`.`annual_fee_information_0321` WHERE `application_number` = '{sqh}' LIMIT 1"
            cur2.execute(sql)
            data0 = cur2.fetchall()
            if data0:
                money2 = data0[0][4]
                types = data0[0][3]
            else:
                money2 = "未抓到年费信息"
                types = "未抓到"
            item['m_type'] = types + f"{money2}+滞纳金{money1}"
            # item['money'] = int(float(money1) + float(money2))
            item = get_is_stop(sqh, item)
            item = get_lianxr(item)
            item = get_marks(sqh, item)
            print(item)
            info_list.append(item)
    save_json_dict(info_list)

def get_lianxr(item):
    order_num = item['unique_number']
    sql = f"""SELECT patent_user, liaisons, liaisons_tel FROM `mykhadmin`.`orders` WHERE `order_number` = '{order_num}'"""
    cur1.execute(sql)
    data = cur1.fetchall()

    item['patent_user'] = data[0][0]
    item['lianxr'] = data[0][1]
    item['tel'] = data[0][2]
    item['money'] = ""
    item['state'] = ""
    return item

def get_marks(sqh, item):
    sql = f"SELECT marks FROM `big_data`.`MoneyWarning` WHERE `sqh` = '{sqh}'"
    cur2.execute(sql)
    data = cur2.fetchall()
    marks = data[0][0]
    if marks:
        aa = json.loads(marks)
        if "放弃" in marks:
            item['marks'] = '放弃'
            item['time'] = ""
        else:
            item['marks'] = aa[0]['marks']
            item['time'] = aa[0]['insert_time']
    else:
        item['marks'] = ""
        item['time'] = ""
    return item


def save_json_dict(info_list):
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("sheet1")
    sheet.write(0, 0, '序号')
    sheet.write(0, 1, '申请号')
    sheet.write(0, 2, '专利名字')
    sheet.write(0, 3, '专利权人')
    sheet.write(0, 4, '费用类型')
    sheet.write(0, 5, '金额')
    sheet.write(0, 6, '截止日')
    sheet.write(0, 7, '状态')
    sheet.write(0, 8, '联系人')
    sheet.write(0, 9, '电话')
    sheet.write(0, 10, "备注")
    sheet.write(0, 11, 'is_stop')
    sheet.write(0, 12, '备注时间')
    if len(info_list) > 0:
        for i in range(len(info_list)):
            sheet.write(i + 1, 0, i + 1)
            sheet.write(i + 1, 1, info_list[i]['sqh'])
            sheet.write(i + 1, 2, info_list[i]['name'])
            sheet.write(i + 1, 3, info_list[i]['patent_user'])
            sheet.write(i + 1, 4, info_list[i]['m_type'])
            sheet.write(i + 1, 5, info_list[i]['money'])
            sheet.write(i + 1, 6, info_list[i]['jzr'])
            sheet.write(i + 1, 7, info_list[i]['state'])
            sheet.write(i + 1, 8, info_list[i]['lianxr'])
            sheet.write(i + 1, 9, info_list[i]['tel'])
            sheet.write(i + 1, 10, info_list[i]['marks'])
            sheet.write(i + 1, 11, info_list[i]['is_stop'])
            sheet.write(i + 1, 12, info_list[i]['time'])
        workbook.save('./滞纳金.xls')


if __name__ == '__main__':
    deal_znj()

